use v6.d;

use lib './';
use DB::SQLite;
use Rule::Table;

=begin pod
class Where-Clause {
    ...
}

class DML { ... }

class DDL { ... }
=end pod


role Tabu
{     
    
	# DDL语句
        has Str $.ddl is required;
        has Str $.source is required;
	
	# 连接数据库 
	# Praram: $source -- Data Source Name
	# Return: DB 对象
	method connect( --> DB::Connection) { ... }

	# 表名
	method table-name() {
        my $ddl = self.ddl;
	    my $table-name = Rule::Table.parse($ddl).<table-name>.Str;
	    return $table-name;
	}

	# 初始化表
	# Param: 
	# Return: Bool值 -- True | False
	method init(Str $source --> Bool) { ... }

	# 选择列
	# Param:  @columns -- 列名
	#         $where   -- 选择条件 哈希值 {$column-name => $value}
	# Return: ResultSet 对象
	method select( @columns, $where --> DB::Result) { ... }

	# 更新列
	# Param:  %hashes -- 更新的列名及值的哈希对 { $column-name => $value}
	#         $where  -- 选择条件 哈希值 { $column-name => $value }
	# Return: Int值   -- 已更新的行数
	method update(Pair $kv, Pair $where --> Int) {...}

	# 删除列
	# Param:  $where  -- 选择条件 哈希值 {$column-name => $value}
	# Return: Int值   -- 已删除的行数
	method delete(Pair $where) returns Int {...}

	# 插入数据
	# Param:  @columns -- 插入数据的列名
	#         @values  -- 插入数据的值
	# Return: Int值    -- 已插入的行数 
	method insert( Str @columns, @values --> Int) {...}

	# 关闭数据库连接
	method finish() {
	    self.connect.finish;
	}
}

class Table does Tabu {

	my $connection;

	
  
    multi method connect(Str $source --> DB::Connection) {
        $connection = DB::SQLite.new(filename=>$source).db;
		return $connection;
	}

    multi method connect( --> DB::Connection) {
		#$connection = DB::SQLite.new(filename=>$!source).db ;
	    return  $connection;
    }

    method init(-->Bool) {
		my $drop = sprintf("DROP TABLE IF EXISTS %s", self.table-name);
		my $db = self.connect;
		$db.execute($drop);
	    return  $db.execute($!ddl).defined;
    }

    method select( @columns, Pair $where --> DB::Result) {
        my $con = self.connect;
	    my $stmt = sprintf("SELECT %s FROM %s WHERE %s = ?", 
		                        @columns eqv ['ALL'] ?? '*' !! @columns.join(", "),
								self.table-name,
								$where.key);
	    my $sth will leave {.finish} = $con.prepare($stmt);
	    return $sth.execute($where.value);
		
    }
    
    method update(Pair $kv, Pair $where --> Int) {
        my $con = self.connect;
		my $val = $kv.value ~~ Num ?? $kv.value !! "'" ~ $kv.value.Str ~ "'";
		my $filt = $where.value ~~ Num ?? $where.value !! "'" ~ $where.value.Str ~ "'";
	    my $stmt = sprintf("UPDATE %s SET %s = %s  WHERE %s = %s", self.table-name, $kv.key, $val, $where.key,$filt);
	    my $sth will leave {.finish} = $con.prepare($stmt);
		#say $stmt;

	    $sth.execute;
    }

    method delete(Pair $where --> Int) {
        my $con = self.connect;
	    my $stmt = sprintf("DELETE FROM %s WHERE %s = ?", self.table-name, $where.key);
	    my $sth  = $con.prepare($stmt);
	    return $sth.execute($where.value, :finish);
    }

    method insert( @columns, @values --> Int)  {
        my $con = self.connect;
	    my @a;
		@a.append("?") for ^@columns;
	    my $stmt = sprintf("INSERT INTO %s (%s) VALUES (%s)", self.table-name, @columns.join(", "), @a.join(", "));
	    my $sth will leave {.finish} = $con.prepare($stmt);
	    $sth.execute(| @values);
    }

	submethod TWEAK {$connection = self.connect($!source);}

	submethod DESTROY { $connection.free }
} 

sub MAIN() {
    my $ddl = 'CREATE TABLE test ( id INT PRIMARY KEY, name VARCHAR(60) NOT NULL)';
    my $so = "./my.db";
    my $tab = Table.new(ddl => $ddl, source => $so);
    say $tab.init();
    say $tab.insert(('id', 'name') , (1, 'Bill-Gates'));
    say $tab.insert([ 'id', 'name'], (2, "Elon-Mask"));
    say $tab.select([ 'id', 'name'], Pair.new('id', 1)).hash;
	say $tab.update(Pair.new('name','Steve-Jobs'), Pair.new('id', 1));
	say $tab.delete(Pair.new('name' , 'Elon-Mask'));
	my $source = './mydata.sqlite3';
    my $string = q:to/STR/;
CREATE TABLE Info2 (
    aab001 VARCHAR(12) NOT NULL,
    单位名称 VARCHAR(60),
    单位编号 VARCHAR(14),
    dwbm VARCHAR(5),
    dwmc VARCHAR(60),
    scbh VARCHAR(11),
    个人编号 VARCHAR(14) NOT NULL PRIMARY KEY,
    姓名 VARCHAR(60) NOT NULL,
    身份证号 VARCHAR(18) NOT NULL,
    性别 VARCHAR(6),
    民族 VARCHAR(60),
    出生日期 VARCHAR(12),
    工作日期 VARCHAR(12),
    退休日期 VARCHAR(12),
    参保状态 VARCHAR(10),
    缴费状态 VARCHAR(10),
    首次参保 VARCHAR(12),
    本次参保 VARCHAR(12),
    缴费基数 DECIMAL(12,2),
    养老金  DECIMAL(12,2),
    发放账号 VARCHAR(30),
    发放机构 VARCHAR(60),
    TQBZ VARCHAR(10),
    MDDW VARCHAR(10),
    LX2013 VARCHAR(10),
    备注 VARCHAR(200)
)
STR
	my $table = Table.new(:ddl($string),:source($source));
	say $table.select(['个人编号','姓名','身份证号','出生日期','缴费基数','单位名称'],Pair.new('姓名','刘新行')).hash;
    say $table.select(['ALL'], Pair.new('姓名', '陈颖')).hash;
}
